
package DBLayer;
import java.sql.*;
import ModelLayer.Supplier;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBSupplier implements IFDBSupplier
{
    private Connection con;
    private PreparedStatement pStmtSelect;
    
    /**
     * Constructor for class DBSupplier
     */
    public DBSupplier()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    /**
     * Method to get all Suppliers in the database
     * @param retrieveAssociation
     * @return returns an ArrayList with Suppliers
     */
    @Override
    public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation)
    {
        return findSuppliersWithPreQuery(null,"",retrieveAssociation);
    }
    
    /**
     * Method to find a Supplier with a specific id
     * @param supplierId int supplierId the id of the Supplier
     * @param retrieveAssociation
     * @return returns a Supplier Object 
     */
    @Override
    public Supplier findSupplier(int supplierId, boolean retrieveAssociation)
    {   
        String wClause = "  where s.supplierId = ";
        return findWithPreQuery(supplierId, wClause, retrieveAssociation);
    }
    
    /**
     * Method to insert a Supplier into the database
     * @param sObj an Object of the type Supplier
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int insertSupplier(Supplier sObj)
    {
        int nextSupplierId = getMaxId();
        nextSupplierId = nextSupplierId + 1;
        System.out.println("next supplierId = " +  nextSupplierId);
        PreparedStatement pstmt = null;
        int rc = -1; 
            
        String insertSupplier = "insert into Supplier (supplierId, name, address, email, accountInfo, zipCode, cvr, comment, sellerName, sellerPhoneNo, sellerMail, typeNo, kreNo, cusNo, phoneNo, mobileNo)"+
                     "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        System.out.println(insertSupplier);
        try
        {
            pstmt = con.prepareStatement(insertSupplier);
            pstmt.setInt(1,nextSupplierId);
            pstmt.setString(2,sObj.getName());
            pstmt.setString(3, sObj.getAddress());
            pstmt.setString(4,sObj.getEmail()); 
            pstmt.setString(5, sObj.getAccountInfo());
            pstmt.setInt(6, sObj.getZipCode());
            pstmt.setString(7, sObj.getCvr());
            pstmt.setString(8, sObj.getComment());
            pstmt.setString(9, sObj.getSellerName());
            pstmt.setString(10, sObj.getSellerPhoneNo());
            pstmt.setString(11, sObj.getSellerMail());
            pstmt.setString(12, sObj.getTypeNo());
            pstmt.setString(13, sObj.getDebNo());
            pstmt.setString(14, sObj.getCusNo());
            pstmt.setString(15, sObj.getPhoneNo());
            pstmt.setString(16, sObj.getMobileNo());
                
            rc = pstmt.executeUpdate();
        }//end try
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
                         
        catch(Exception e)
        {
            System.out.println("Insert exception in Supplier db: "+e.getMessage());
        }
        return rc; 
    }
    
    /**
     * Method to update a supplier in the database;
     * @param supplier an Object of the type Supplier
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int updateSupplier(Supplier supplier)
    {
        Supplier suppObj  = supplier;
	int rc=-1;
        PreparedStatement pstmt = null;

	String query="UPDATE Supplier SET "+
                "name = ?, "+
		"address = ?, "+
                "email = ?, "+
                "accountInfo = ?, "+
                "zipCode = ?, "+
                "cvr = ?, "+
                "comment = ?, "+
                "typeNo = ?, "+
                "kreNo = ?, "+
                "cusNo = ?, "+
                "sellerName = ?, "+
                "sellerPhoneNo = ?, "+
                "sellerMail = ?, "+
                "phoneNo = ?, "+
                "mobileNo = ? "+
		" WHERE supplierId = ?";
        System.out.println("Update query:" + query);
        try
        {
            //update employee
            pstmt = con.prepareStatement(query);
                    
            pstmt.setString(1, suppObj.getName());
            pstmt.setString(2, suppObj.getAddress());
            pstmt.setString(3, suppObj.getEmail());
            pstmt.setString(4, suppObj.getAccountInfo());
            pstmt.setInt(5, suppObj.getZipCode());
            pstmt.setString(6, suppObj.getCvr());
            pstmt.setString(7, suppObj.getComment());
            pstmt.setString(8, suppObj.getTypeNo());
            pstmt.setString(9, suppObj.getDebNo());
            pstmt.setString(10, suppObj.getCusNo());
            pstmt.setString(11, suppObj.getSellerName());
            pstmt.setString(12, suppObj.getSellerPhoneNo());
            pstmt.setString(13, suppObj.getSellerMail());
            pstmt.setString(14, suppObj.getPhoneNo());
            pstmt.setString(15, suppObj.getMobileNo());
            pstmt.setInt(16, suppObj.getSupplierId());
	 		
            rc = pstmt.executeUpdate();
            pstmt.close();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Delete exception in Supplier db: "+e.getMessage());
        }
        
        return rc;
    }
    
    /**
     * Method to delete a supplier from the database
     * @param supplierId int supplierId the id of the Supplier
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int deleteSupplier(int supplierId)
    {
        int rc=-1;
        String query="DELETE FROM Supplier WHERE supplierId = '" +
                supplierId + "'";
				
        System.out.println(query);
	try
        {
            // delete from employee
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//slut try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Delete exception in Supplier db: "+e.getMessage());
        }
        
        return rc; 
    }
	
    /**
     * Method to get arraylist with all Suppliers
     * @param valueObject value the value which is used to search, in this case an int id
     * @param wClause String wClause
     * @param retrieveAssociation
     * @return returns suppliers an arryList with Supplier
     */
    private ArrayList<Supplier> findSuppliersWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
    {
        System.out.println("Find with preCompiled");
        int supplierId = 0;
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);          
        }
        ArrayList<Supplier> suppliers = new ArrayList<Supplier>();
        ResultSet results;
        try
        {    
            if(!wClause.equals(""))
            {
                supplierId = (Integer) value;
                pStmtSelect.setInt(1,supplierId);
            }
            
            results = pStmtSelect.executeQuery();
            while( results.next() )
            {
                Supplier suppObj = new Supplier();
		suppObj = buildSupplier(results, retrieveAssociation);	
                suppliers.add(suppObj);
            }//end while  
        }//end while
        catch(Exception e)
        {
            System.out.println("findStandardTasksWithPreQuery exception in Supplier db: "+e.getMessage());
        }
        
        return suppliers;
    }
    
    /**
     * Method to build an object of the type Supplier
     * @param results ResultSet 
     * @param retrieveAssociation
     * @return returns a Supplier object
     */
    private Supplier buildSupplier(ResultSet results, boolean retrieveAssociation)
    {
       Supplier suppObj = new Supplier();
       try
       {
           if(retrieveAssociation)
           {
               suppObj.setSupplierId(results.getInt("supplierId"));
               suppObj.setName(results.getString("name"));
               suppObj.setAddress(results.getString("address"));
               suppObj.setPhoneNo(results.getString("phoneNo"));
               suppObj.setMobileNo(results.getString("mobileNo"));
               suppObj.setEmail(results.getString("email"));
               suppObj.setAccountInfo(results.getString("accountInfo"));
               suppObj.setZipCode(results.getInt("zipCode"));
               suppObj.setCvr(results.getString("cvr"));
               suppObj.setComment(results.getString("comment"));
               suppObj.setTypeNo(results.getString("typeNo"));
               suppObj.setDebNo(results.getString("kreNo"));
               suppObj.setCusNo(results.getString("cusNo"));
               suppObj.setSellerName(results.getString("sellerName"));
               suppObj.setSellerPhoneNo(results.getString("sellerPhoneNo"));
               suppObj.setSellerMail(results.getString("sellerMail"));
           }//end if
           else
           {
               //no Supplier was found
               suppObj = null;
           }//end else
       }//end try
       catch(Exception e)
       {
           System.out.println("buildSupplier exception in Supplier db: "+e.getMessage());
       }
        
        return suppObj;
    }
    
    /**
     * Method to create a prepared statement 
     * @param wClause String wClause
     * @return returns a PreparedStatment
     */
    private PreparedStatement getPreparedStmt(String wClause)
    {
        String query = "";
        try
        {
            if(wClause.equals(""))
            {
                query = "Select * FROM Supplier as s join ZipCode as p on s.zipCode = p.zipCode";
            }//end if
            else
            {
                query = "SELECT * FROM Supplier as s join ZipCode as p on s.zipCode = p.zipCode" +
                    wClause + " ?";
            }//end else
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
        }//end try
        catch(Exception e)
        {
            System.out.println("getPreparedStmt exception in Supplier db: "+e.getMessage());
        }
        return pStmtSelect;
    }
    
    /**
     * Method that finds a StandardTask in the database 
     * @param supplierId int supplierId the id of the Supplier
     * @param wClause String wClause
     * @param retrieveAssociation
     * @return returns a Supplier Object
     */
    private Supplier findWithPreQuery(int supplierId, String wClause, boolean retrieveAssociation)
    {
        System.out.println("findWithPreQuery");
        
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);
        }
        Supplier suppObj = null;
        ResultSet results;

        try
        {
            pStmtSelect.setInt(1,supplierId);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                suppObj = buildSupplier(results, retrieveAssociation);
            }
        }
        catch(Exception e)
        {
            System.out.println("findWithPreQuery exception in Supplier db: "+e.getMessage());
        }
        return suppObj;
    }
    
    /**
     * Method to get the highest SupplierId from the database
     * @return returns an int which is the highest supplierId from the database
     */
    public int getMaxId()
    {
        int maxSupplieryId = GetMax.getMaxId("Select max(supplierId) from Supplier");
        return maxSupplieryId;
    }
}